<?php
namespace app\command\logic;

use think\console\Output;

/**
 *
 */
class StatsLogic
{

    /**
     * php think cli stats_member
     * php think cli stats_member --msg='{"days":1}'
     *
     * @param  Output $output [description]
     * @param  array  $msg    [description]
     * @return [type]         [description]
     */
    public static function statsMember(Output $output, $msg = [])
    {
        $insertData = [];
        $days       = intval($msg['days'] ?? 1);
        for ($i = 0; $i < $days; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            // echo $date . "\n";exit;
            self::dataForMemberConsumed($date);
            self::dataForMemberArea($date);
            $insertData[] = self::dataForMember($date);
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_member')
                ->replace()
                ->insertAll($insertData);
        }
    }

    private static function dataForMember($date)
    {
        $end = $date . ' 23:59:59';

        // agency_type 1省代 2市代 3区代 4 区代
        $field = [
            'COALESCE(SUM(CASE WHEN agency_type IN (3, 4) THEN 1 ELSE 0 END), 0) AS agency_3_count',
            'COALESCE(SUM(CASE WHEN agency_type = 2 THEN 1 ELSE 0 END), 0) AS agency_2_count',
            'COALESCE(SUM(CASE WHEN agency_type = 1 THEN 1 ELSE 0 END), 0) AS agency_1_count',
        ];
        $data = \think\facade\Db::table('member_agency')
            ->field($field)
            ->where('deleted', 0)
        // 状态     1正常   0取消
            ->where('status', 1)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('member_agency')->getlastsql();
        // var_dump($data);exit;
        $data['date'] = $date;

        // give_member_level 赠送会员等级 空表示不赠送 vip 赠送VIP  partner 赠送服务商
        $field2 = [
            'COALESCE(SUM(CASE WHEN give_member_level = \'vip\' THEN 1 ELSE 0 END), 0) AS up_vip_count',
            'COALESCE(SUM(CASE WHEN give_member_level = \'partner\' THEN 1 ELSE 0 END), 0) AS up_partner_count',
        ];
        $data2 = \think\facade\Db::table('packs_order')
            ->field($field2)
            ->where('deleted', 0)
        // 状态     1正常   0取消
            ->where('status', 1)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('packs_order')->getlastsql();
        // var_dump($data2);exit;

        // use index
        $data['order_member_count'] = \think\facade\Db::table('order')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->count('distinct user_id');
        // echo \think\facade\Db::table('order')->getlastsql();
        // var_dump($data['order_member_count']);exit;

        // 访问人数
        $data['view_count'] = \think\facade\Db::table('member_oplog')
            ->whereBetweenTime('create_at', $date, $end)
            ->count('distinct user_id');

        // use index
        $data['amount'] = \think\facade\Db::table('member')
            ->where('create_at', '<=', $end)
            ->count();
        $data['new_count'] = \think\facade\Db::table('member')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;
        $data['signin_count'] = \think\facade\Db::table('community_sign_user')
        // ->where('type', 1) //状态 1社区签到
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 审核拒绝商家
        $data['shop_rjection_count'] = \think\facade\Db::table('system_oplog')
            ->where('action', '商家审核拒绝')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // echo \think\facade\Db::table('system_oplog')->getlastsql();exit;

        // 审核通过商家
        $data['shop_approved_count'] = \think\facade\Db::table('member_shop_apply')
            ->where('deleted', 0)
            ->where('status', 1) // 0 审核中 1 成功 2 拒绝
            ->whereBetweenTime('auth_at', $date, $end)
            ->count();

        // 申请商家
        $data['shop_apply_count'] = \think\facade\Db::table('member_shop_apply')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        return array_merge($data, $data2);
    }

    private static function dataForMemberArea($date)
    {
        $end = $date . ' 23:59:59';

        $field = [
            'id',
            'province_id',
            'city_id',
            'country_id',
            'create_at',
        ];
        $rows = \think\facade\Db::table('member')
            ->field($field)
            ->where('create_at', '<=', $end)
            ->select()
            ->toArray();
        if (empty($rows)) {
            return [];
        }
        $reg_total = [];
        $startT    = strtotime($date);
        $endT      = strtotime($end);
        foreach ($rows as $row) {
            $create_at = strtotime($row['create_at']);

            $isNew = $create_at >= $startT && $create_at <= $endT ? 1 : 0;
            $k     = $row['country_id'];
            if (isset($reg_total[$k])) {
                $reg_total[$k]['amount'] += 1;
                $reg_total[$k]['new_count'] += $isNew;
            } else {
                $name1 = \think\facade\Db::table('area')
                    ->where('this_id', $row['province_id'])
                    ->value('name');
                $name2 = \think\facade\Db::table('area')
                    ->where('this_id', $row['city_id'])
                    ->value('name');
                $name3 = \think\facade\Db::table('area')
                    ->where('this_id', $row['country_id'])
                    ->value('name');
                $reg_total[$k] = [
                    'amount'     => 1,
                    'area_ids'   => implode('_', [
                        $row['province_id'],
                        $row['city_id'],
                        $row['country_id'],
                    ]),
                    'area'       => implode(' ', [$name1, $name2, $name3]),
                    'new_count'  => $isNew,
                    'date'       => $date,
                    'view_count' => 0,
                ];
            }
        }
        $field = [
            'province',
            'city',
            'district',
            'adcode country_id',
            'count(distinct user_id) AS view_count',
        ];
        $rows = \think\facade\Db::table('member_reverse_geocoding')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->group('adcode')
            ->select()->toArray();
        // echo json_encode($rows, 320);exit;
        if ( ! empty($rows)) {
            foreach ($rows as $row) {
                try {
                    $k = $row['country_id'];
                    if (isset($reg_total[$k])) {
                        $reg_total[$k]['view_count'] = $row['view_count'];
                    } else {
                        $city_id = $row['country_id'] > 0?\think\facade\Db::table('area')
                            ->where('this_id', $row['country_id'])
                            ->value('p_id') : 0;
                        $province_id = $city_id > 0?\think\facade\Db::table('area')
                            ->where('this_id', $city_id)
                            ->value('p_id') : 0;

                        $reg_total[$k] = [
                            'amount'     => 0,
                            'new_count'  => 0,
                            'area_ids'   => implode('_', [
                                $province_id,
                                $city_id,
                                $row['country_id'],
                            ]),
                            'area'       => implode(' ', [
                                $row['province'],
                                $row['city'],
                                $row['district'],
                            ]),
                            'date'       => $date,
                            'view_count' => $row['view_count'],
                        ];
                    }
                } catch (\Exception $e) {
                    var_dump($e->getMessage());
                    var_dump($e->getTrace());
                    exit;
                }
            }
        }

        // var_dump($reg_total);exit;
        foreach ($reg_total as $row) {
            \think\facade\Db::table('stats_member_area')
                ->replace()
                ->insert($row);
        }
        // echo json_encode($reg_total, 320);exit;
        // try {
        //     \think\facade\Db::table('stats_member_area')
        //         ->replace()
        //         ->insertAll($reg_total);
        // } catch (\Exception $e) {
        //     echo json_encode($e->getTrace(), 320);
        // }

        return true;
    }

    private static function dataForMemberConsumed($date)
    {
        $end = $date . ' 23:59:59';

        // status 0待支付 1已支付 2已取消
        $field = [
            'user_id',
            'COALESCE(SUM(CASE WHEN status = 1 THEN pay_price ELSE 0 END), 0) AS pay_price',
            'COALESCE(count(CASE WHEN status = 1 THEN 1 ELSE 0 END), 0) AS orde_count',
        ];
        $rows = \think\facade\Db::table('order_pay')
            ->field($field)
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->group('user_id')
            ->select()->toArray();
        if (empty($rows)) {
            return true;
        }
        // echo \think\facade\Db::table('order_pay')->getlastsql();
        // var_dump($rows);exit;
        $user_ids = array_column($rows, 'user_id');
        $userMap  = \think\facade\Db::table('member')
            ->whereIn('id', $user_ids)
            ->column(['id', 'level', 'user_name'], 'id');
        // var_dump($userMap);exit;

        $insertData = [];
        foreach ($rows as $row) {
            $user             = $userMap[$row['user_id']] ?? [];
            $row['date']      = $date;
            $row['level']     = $user['level'] ?? 0;
            $row['user_name'] = $user['user_name'] ?? '';
            $insertData[]     = $row;
        }
        \think\facade\Db::table('stats_member_consumed')
            ->replace()
            ->insertAll($insertData);
        return true;
    }

    /**
     * php think cli stats_community
     * php think cli stats_community --msg='{"days":30}'
     *
     * @param  Output $output [description]
     * @param  array  $msg    [description]
     * @return [type]         [description]
     */
    public static function statsCommunity(Output $output, $msg = [])
    {
        // var_dump($msg);exit;
        $insertData = [];
        $days       = intval($msg['days'] ?? 1);
        for ($i = 0; $i < $days; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            // echo $date . "\n";exit;
            $insertData[] = self::dataForCommunity($date);
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_community')
                ->replace()
                ->insertAll($insertData);
        }
    }

    private static function dataForCommunity($date)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // sign_status 1未使用 2入驻已使用 3已返还到余额
        $field = [
            'COALESCE(count(distinct user_id), 0) AS signin_count',
            'COALESCE(count(CASE WHEN sign_status IN (2,3) THEN 1 ELSE 0 END), 0) AS real_signin_count',
        ];
        $data = \think\facade\Db::table('community_sign_user')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        // 社区数总数
        $field2 = [
            'count(*) as community_total', // 社区广场
            'COALESCE(sum(CASE WHEN type = 0 THEN 1 ELSE 0 END), 0) AS square_total', // title: '社区广场'
            'COALESCE(sum(CASE WHEN type = 1 THEN 1 ELSE 0 END), 0) AS new_count', // title: '新人场'
            'COALESCE(sum(CASE WHEN type = 2 THEN 1 ELSE 0 END), 0) AS activity_count', // title: '活动场'
        ];
        $data2 = \think\facade\Db::table('community_order')
            ->field($field2)
            ->where('last_order_id', 0) // 上次id 为0 排除重复社区
            ->where('status', 2) // 1未支付 2已支付 3取消
        // ->where('create_at', '<=', $end)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('community_order')->getlastsql();exit;
        $field3 = [
            'count(distinct user_id) as participant_count', // 参与人数
            // 'COALESCE(count(CASE WHEN status = 2 THEN (distinct user_id) ELSE 0 END), 0) AS real_participant_count', // title: '实际参与人数'
            'COALESCE(COUNT(DISTINCT CASE WHEN status = 2 THEN user_id END), 0) AS real_participant_count',
            'COALESCE(sum(price), 0) AS order_price_count', // title: '订单总金额'
            'COALESCE(sum(pay_price), 0) AS sand_pay_count', // title: '电子钱包'
            'COALESCE(sum(amount_price), 0) AS community_pay_amount', // 余额
            'COALESCE(sum(premium), 0) AS all_revenue', // 总溢价
        ];
        $data3 = \think\facade\Db::table('community_order')
            ->field($field3)
        // ->where('create_at', '<=', $end)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();

        $field4 = [
            'COALESCE(sum(pay_price), 0) AS all_fee', // 总服务费
        ];
        $data4 = \think\facade\Db::table('community_op_order')
            ->field($field4)
            ->where('is_pay', 1) // 是否支付 0否 1是
        // ->where('pay_at', '<=', $end)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();

        // 总波比 奖励出去的金额
        // 平台收益 = 总溢价 - 总波比
        $field5 = [
            // 'COALESCE(sum(amount_price), 0) AS sys_revenue', // 平台收益 TODO
            'COALESCE(sum(amount), 0) AS all_rate', // 总波比
        ];
        $data5 = \think\facade\Db::table('member_community_amount_log')
            ->field($field5)
            ->where('status', 1) // 1加 2减少
            ->whereBetweenTime('create_at', $date, $end)
            ->find();

        // 签到页浏览数
        $data['signin_view_count'] = \think\facade\Db::table('member_oplog')
        // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->where('api', '/api/community/signInfo')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 社区广场浏览数
        $data['square_view_count'] = \think\facade\Db::table('member_oplog')
        // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->where('api', '/api/community/communityPayList')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 社区点击竞拍数
        $data['community_click_count'] = \think\facade\Db::table('member_oplog')
        // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->where('api', '/api/community/click')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        $data['sys_revenue'] = $data3['all_revenue'] - $data5['all_rate'];
        // 收益百分比（平台收益/总溢价
        $data['revenue_rate'] = $data3['all_revenue'] != 0 ? $data['sys_revenue'] / $data3['all_revenue'] : 0;
        $data['date']         = $date;
        // var_dump($data);exit;
        return array_merge($data, $data2, $data3, $data4, $data5);
    }

    /**
     * php think cli stats_goods --msg='{"days":30}'
     *
     * @param  Output $output [description]
     * @param  array  $msg    [description]
     * @return [type]         [description]
     */
    public static function statsGoods(Output $output, $msg = [])
    {
        // var_dump($msg);exit;
        $insertData = [];
        $days       = intval($msg['days'] ?? 1);
        for ($i = 0; $i < $days; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            // echo $date . "\n";exit;
            $insertData[] = self::dataForGoods($date);

            self::dataForGoodsSpu($date);
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_goods')
                ->replace()
                ->insertAll($insertData);
        }
    }

    /**
     * 统计商品数据
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function dataForGoods($date)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field = [
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods',
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods',
        ];
        $data = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        $data['date'] = $date;
        // use index idx_Status_Deleted_Type_Category_Sales
        $data['sale_goods'] = \think\facade\Db::table('goods')
            ->where('deleted', 0)
            ->where('status', 1) // 0审核中 1上架 2下架
            ->where('create_at', '<', $end)
            ->count();
        $data['sale_goods'] += \think\facade\Db::table('supply_goods')
            ->where('deleted', 0)
            ->where('status', 0) // 商品状态：0、已上架1、已下架'
        // ->where('create_at', '<', $end)
            ->count();
        // echo \think\facade\Db::table('goods')->getlastsql();exit;
        // use index idx_Api_CreateAt
        $data['view_count'] = \think\facade\Db::table('member_oplog')
            ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;

        $data['add_cart'] = \think\facade\Db::table('member_cart')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->sum('quantity');

        // echo \think\facade\Db::table('order_goods')->getlastsql();exit;
        $data['rate'] = $data['pay_goods'] > 0 ? $data['view_count'] / $data['pay_goods'] : 0; //转换率 (浏览量/支付件数)
        return $data;
    }
    /**
     * 统计商品数据排行
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function dataForGoodsSpu($date)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field = [
            'goods_id',
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods',
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods',
        ];
        $items = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->where('deleted', 0)
            ->group('goods_id')
            ->select()->toArray();
        if (empty($items)) {
            return [];
        }
        $goods_ids = array_column($items, 'goods_id');
        // var_dump($goods_ids);exit;
        $goodsMap = \think\facade\Db::table('goods')
            ->whereIn('id', $goods_ids)
            ->column('id,goods_title,view_count', 'id');
        // echo json_encode($goodsMap, 320);exit;
        $insertDataSpu = [];
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        foreach ($items as $key => $data) {
            $g = $goodsMap[$data['goods_id']] ?? [];
            if (empty($g)) {
                continue;
            }
            $data['goods_title'] = $g['goods_title'];
            // $data['view_count']  = $g['view_count'];

            $data['view_count'] = \think\facade\Db::table('member_oplog')
            // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
                ->where('api', '/api/goods/goodsDetail')
                ->where('params', 'like', '%"' . $data['goods_id'] . '"%')
                ->whereBetweenTime('create_at', $date, $end)
                ->count();
            $data['rate'] = $data['pay_goods'] > 0 ? $data['view_count'] / $data['pay_goods'] : 0; //转换率 (浏览量/支付件数)

            // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;
            $data['add_cart'] = \think\facade\Db::table('member_cart')
                ->where('deleted', 0)
                ->where('goods_id', $data['goods_id'])
                ->whereBetweenTime('create_at', $date, $end)
                ->sum('quantity');

            $data['date'] = $date;

            $insertDataSpu[] = $data;
        }

        if ( ! isset($data['pay_goods'])) {
            var_dump($data);exit;
        }
        \think\facade\Db::table('stats_goods_spu')
            ->replace()
            ->insertAll($insertDataSpu);

        return $data;
    }

    /**
     * 统计 订单交易数据
     * php think cli stats_transaction --msg='{"days":3}'
     *
     * @param  Output $output [description]
     * @param  array  $msg    [description]
     * @return [type]         [description]
     */
    public static function statsTransaction(Output $output, $msg = [])
    {
        // var_dump($msg);exit;
        $insertData = [];
        $days       = intval($msg['days'] ?? 1);
        for ($i = 0; $i < $days; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            // echo $date . "\n";exit;
            $insertData[] = self::dataForTransaction($date);
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_transaction')
                ->replace()
                ->insertAll($insertData);
        }
    }

    private static function dataForTransaction($date)
    {
        $end = $date . ' 23:59:59';

        // 订单状态 0待支付 1待发货 2待收货 3已收货 4已完成 5退款中 6退款成功 7取消 8超时取消 9待退货
        //   `order_type` 1线下直付单 2线上单 3线下单 4供应链',

        $field = [
            // 'count(*) as order_total',
            'COALESCE(SUM(CASE WHEN order_type = 2 THEN 1 ELSE 0 END), 0) AS online_order_count',
            'COALESCE(SUM(CASE WHEN order_type = 3 THEN 1 ELSE 0 END), 0) AS offline_order_count',
            'COALESCE(SUM(CASE WHEN order_type = 4 THEN 1 ELSE 0 END), 0) AS supply_order_count',

            'COALESCE(SUM(pay_price), 0) AS order_total_price',
            'COALESCE(SUM(CASE WHEN order_type = 2 THEN pay_price ELSE 0 END), 0) AS online_order_price',
            'COALESCE(SUM(CASE WHEN order_type = 3 THEN pay_price ELSE 0 END), 0) AS offline_order_price',
            'COALESCE(SUM(CASE WHEN order_type = 4 THEN pay_price ELSE 0 END), 0) AS supply_order_price',

            'COALESCE(COUNT(DISTINCT CASE WHEN order_type = 2 THEN user_id END), 0) AS online_member_count', // 线上下单人数
            'COALESCE(COUNT(DISTINCT CASE WHEN order_type = 3 THEN user_id END), 0) AS offline_member_count', // 线下下单人数
        ];
        $data = \think\facade\Db::table('order')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('order')->getlastsql();
        // var_dump($data);exit;
        $data['date'] = $date;

        // 社区下单人数
        $data['community_order_member_count'] = \think\facade\Db::table('community_order')
            ->whereBetweenTime('create_at', $date, $end)
            ->count('distinct user_id');
        $data['community_order_count'] = \think\facade\Db::table('community_order')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        $data['gift_order_count'] = \think\facade\Db::table('packs_order')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        $data['community_order_price'] = \think\facade\Db::table('community_order')
            ->whereBetweenTime('create_at', $date, $end)
            ->sum('pay_price');
        $data['gift_order_price'] = \think\facade\Db::table('packs_order')
            ->whereBetweenTime('create_at', $date, $end)
            ->sum('real_price');

        // 提现金额
        // 提现状态(0失败,1待审核,2已审核,3打款中,4已打款,5已收款)
        $data['balance_withdrawal_count'] = \think\facade\Db::table('data_user_transfer')
            ->whereIn('status', [2, 3, 4, 5])
            ->whereBetweenTime('create_at', $date, $end)
            ->sum('amount');

        // 退款金额
        // 售后单状态(0进行中 1 已拒绝 2 财务退款  3 已完成 4已取消)
        $data['refund_price'] = \think\facade\Db::table('order_refund')
            ->whereBetweenTime('create_at', $date, $end)
            ->whereIn('status', [2, 3])
            ->sum('refund_money');

        // 1 琅狼积分兑换  2 数字资产兑换  4 贡献值兑换
        $field2 = [
            'COALESCE(SUM(CASE WHEN exchange_type = 1 THEN 1 ELSE 0 END), 0) AS langlang_score_count',
            'COALESCE(SUM(CASE WHEN exchange_type = 2 THEN 1 ELSE 0 END), 0) AS digital_money_count',
            'COALESCE(SUM(CASE WHEN exchange_type = 4 THEN 1 ELSE 0 END), 0) AS gxz_score_count',
            'COALESCE(SUM(CASE WHEN exchange_type = 1 THEN pay_coin ELSE 0 END), 0) AS langlang_score_price',
            'COALESCE(SUM(CASE WHEN exchange_type = 2 THEN pay_coin ELSE 0 END), 0) AS digital_money_price',
            'COALESCE(SUM(CASE WHEN exchange_type = 4 THEN pay_coin ELSE 0 END), 0) AS gxz_score_price',
        ];
        $data2 = \think\facade\Db::table('exchange_order')
            ->field($field2)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();

        // 线下浏览数
        $data['offline_view_count'] = \think\facade\Db::table('member_oplog')
            ->whereIn('api', ['/api/shop/shopDetail?shoptype=1', '/api/shop/shopDetail'])
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // 线上浏览数
        $data['online_view_count'] = \think\facade\Db::table('member_oplog')
            ->where('api', '/api/shop/shopDetail?shoptype=2')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        $data['order_total'] = 0
             + $data['online_order_count']
             + $data['offline_order_count']
             + $data['supply_order_count']
             + $data['community_order_count']
             + $data['gift_order_count']
             + $data2['langlang_score_count']
             + $data2['digital_money_count']
             + $data2['gxz_score_count'];
        return array_merge($data, $data2);

    }

    /**
     * 统计 控制台数据
     * php think cli stats_home --msg='{"days":3}'
     *
     * @param  Output $output [description]
     * @param  array  $msg    [description]
     * @return [type]         [description]
     */
    public static function statsHome(Output $output, $msg = [])
    {
        // var_dump($msg);exit;
        $insertData = [];
        $days       = intval($msg['days'] ?? 1);
        for ($i = 0; $i < $days; $i++) {
            $date = date('Y-m-d', strtotime("-$i days"));
            // echo $date . "\n";exit;
            $insertData[] = self::dataForHome($date);
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_home')
                ->replace()
                ->insertAll($insertData);
        }
    }

    private static function dataForHome($date)
    {
        $end = $date . ' 23:59:59';

        $field = [
            'count(*) AS new_member_count',
            'COALESCE(SUM(CASE WHEN is_vip = 1 THEN 1 ELSE 0 END), 0) AS new_vip_count',
            'COALESCE(SUM(CASE WHEN is_partner = 1 THEN 1 ELSE 0 END), 0) AS new_partner_count',
        ];
        $data = \think\facade\Db::table('member')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();

        $data['date'] = $date;

        $data['new_shop_count'] = \think\facade\Db::table('member_shop')
            ->where('status',1)
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        $field = [
            'count(*) AS community_order_count',
            'COALESCE(SUM(CASE WHEN status = 2 THEN total_amount ELSE 0 END), 0) AS community_order_price',
        ];
        $community = \think\facade\Db::table('community_order')
            ->field($field)
            ->where('status',2)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        $data['community_order_count'] = $community['community_order_count'];


        $field = [
            'count(*) AS packs_order_count',
            'COALESCE(SUM(real_price), 0) AS packs_order_price',
        ];
        $packs = \think\facade\Db::table('packs_order')
            ->field($field)
            ->where('status','>',0)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        $data['packs_order_count'] = $packs['packs_order_count'];
        $field = [
            'COALESCE(SUM(CASE WHEN order_type = 1 or order_type = 3 THEN 1 ELSE 0 END), 0) AS offline_order_count',
            'COALESCE(SUM(CASE WHEN order_type = 1 or order_type = 3 THEN pay_price ELSE 0 END), 0) AS offline_order_price',
            'COALESCE(SUM(CASE WHEN order_type = 2 THEN 1 ELSE 0 END), 0) AS online_order_count',
            'COALESCE(SUM(CASE WHEN order_type = 2 THEN pay_price ELSE 0 END), 0) AS online_order_price',
            'COALESCE(SUM(CASE WHEN order_type = 4 THEN 1 ELSE 0 END), 0) AS supply_order_count',
            'COALESCE(SUM(CASE WHEN order_type = 4 THEN pay_price ELSE 0 END), 0) AS supply_order_price',
            'COALESCE(SUM(CASE WHEN order_type = 1 and order_type = 2 and order_type = 3 and status = 1 THEN 1 ELSE 0 END), 0) AS wait_order_count',
            'COALESCE(SUM(CASE WHEN order_type = 4 and status = 1 THEN 1 ELSE 0 END), 0) AS wait_supply_order_count',
        ];
        $order = \think\facade\Db::table('order')
            ->field($field)
            ->where('status','>',0)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        $data['offline_order_count'] = $order['offline_order_count'];
        $data['online_order_count'] = $order['online_order_count'];
        $data['community_order_price'] = $community['community_order_price'];
        $data['packs_order_price'] = $packs['packs_order_price'];
        $data['online_order_price'] = $order['online_order_price'];
        $data['offline_order_price'] = $order['offline_order_price'];
        $data['supply_order_price'] = $order['supply_order_price'];

        $data['wait_order_count'] = $order['wait_order_count'];
        $data['wait_supply_order_count'] = $order['wait_supply_order_count'];
        $data['wait_shop_count'] = \think\facade\Db::table('member_shop')
            ->where('status','=',0)
            ->count();
        $data['wait_member_withdraw'] = \think\facade\Db::table('member_withdraw')
            ->where('status','=',0)
            ->count();
        $data['wait_shop_withdraw'] = 0;
        return $data;
    }


}
